package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Vector;

import clases.Ranura;
import clases.casillas.Casilla;

public class AdministradorRanuras {

	private static AdministradorRanuras instancia;
	
	private AdministradorRanuras()
	{
	}
	
	public static AdministradorRanuras getInstancia()
	{
		if (instancia == null)
			instancia = new AdministradorRanuras();
		
		return instancia;
	}
	
	public Vector<Ranura> traerTodas() {
		Vector<Ranura> ranuras = new Vector<Ranura>();
		
		try
		{
			Connection con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement s = con.prepareStatement("select nroRanura from Ranuras order by nroRanura");
			
			ResultSet rs = s.executeQuery();
			while (rs.next()) 
			{
				int nroRanura = rs.getInt("nroRanura");
				
				ArrayList<Casilla> casillasDeLaRanura = traerCasillasDeLaRanura(nroRanura);
				
				Ranura ranura = new Ranura(casillasDeLaRanura);
				
				ranuras.add(ranura);
			}
			
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		catch (Exception e)
		{
			System.out.println();
		}
		return ranuras;
	}
	
	private ArrayList<Casilla> traerCasillasDeLaRanura(int nroRanura) {
		ArrayList<Casilla> casillasDeLaRanura = new ArrayList<Casilla>();
		
		try
		{
			Connection con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement s = con.prepareStatement("select casilla from CasillasRanura where nroRanura = ?");
			s.setInt(1, nroRanura);
			
			ResultSet rs = s.executeQuery();
			while (rs.next()) 
			{
				String nombreCasilla = rs.getString("casilla");
				
				Casilla casilla = AdministradorCasilla.getInstancia().traer(nombreCasilla);
				
				casillasDeLaRanura.add(casilla);
			}
			
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		catch (Exception e)
		{
			System.out.println();
		}
		
		return casillasDeLaRanura;
	}

	public void insertarRanura(int nroRanura) {
		Connection con = null;
		try
		{
			con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement s = con.prepareStatement("insert into Ranuras (nroRanura) values (?)");
			
			s.setInt(1, nroRanura);
			
			s.execute();
		}
		catch (Exception e)
		{
			System.out.println();
		}
		finally {
			if (con != null)
				PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}
	
	public void eliminarRanuras() {
		Connection con = null;
		try
		{
			con = PoolConnection.getPoolConnection().getConnection();
			
			PreparedStatement sCasillasRanura = con.prepareStatement("delete CasillasRanura");
			sCasillasRanura.execute();
			
			PreparedStatement sRanuras = con.prepareStatement("delete Ranuras");
			sRanuras.execute();
		}
		catch (Exception e)
		{
			System.out.println();
		}
		finally {
			if (con != null)
				PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}
	
	public void insertarCasillaEnRanura(int nroRanura, Casilla casilla) {
		Connection con = null;
		try
		{
			con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement s = con.prepareStatement("insert into CasillasRanura (nroRanura, casilla) values (?, ?)");
			
			s.setInt(1, nroRanura);
			s.setString(2, casilla.getNombre());
			
			s.execute();
		}
		catch (Exception e)
		{
			System.out.println();
		}
		finally {
			if (con != null)
				PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}
}